﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Dapper;

namespace EMR
{
    public class TemplateDal
    {
        // private SqlHelperDapper sqlHelperDapper;
        public static Template Select(string name)
        {
            //string sql = $"SELECT  *  FROM tb_Template where Name ='{name}'";
            //return (SqlDataReader)SqlHelper.ExecuteQuery(sql);
            using (SqlConnection db = new SqlConnection(SqlHelper.ConnStrings))
            {
                string sql = $"SELECT  *  FROM tb_Template where Name = @name";
                return db.Query<Template>(sql, new { Name = name }).FirstOrDefault();
            }
        }

        public static int AddTemplate(Template template)
        {
            string sql = @"INSERT  tb_Template
                        (Name, IsMultiple, IsMust, AppliesGender, Remark)
                        values(@Name,@IsMultiple,@IsMust ,@AppliesGender ,@Remark) ";
            SqlParameter[] pms = new SqlParameter[]
             {
                    new SqlParameter("@Name", template.Name),
                    new SqlParameter("@IsMultiple", template.IsMultiple),
                    new SqlParameter("@IsMust",template.IsMust),
                    new SqlParameter("@AppliesGender",template.AppliesGender),
                    new SqlParameter("@Remark",template.Remark)

            };
            int rowAffected = 0;
            try
            {
                rowAffected = (int)SqlHelper.ExecuteNonQuery(sql, false, pms);
            }
            catch
            {

            }
            return rowAffected;
        }

        //public static int AddTemplate(Template template)
        // => SqlHelperDapper.GetScalar<int>
        //        (@"INSERT  tb_Template
        //                (Name, IsMultiple, IsMust, AppliesGender, Remark)
        //                values(@Name,@IsMultiple,@IsMust ,@AppliesGender ,@Remark) ",template
        //        );

        public static int DeleteTemplate(string name  )
         => SqlHelperDapper.GetScalar<int>
                (@"DELETE  tb_Template 
                        WHERE Name = @name ; ", new {Name = name } );

        public static int UPdate(Template template)
        {
            string sql =@"UPDATE tb_Template 
                          SET  IsMultiple=@IsMultiple , IsMust=@IsMust , AppliesGender=@AppliesGender ,Remark=@Remark
                           WHERE  Name =@Name ";
            SqlParameter[] pms = new SqlParameter[]
            {
                    new SqlParameter("@Name", template.Name),
                    new SqlParameter("@IsMultiple", template.IsMultiple),
                    new SqlParameter("@IsMust",template.IsMust),
                    new SqlParameter("@AppliesGender",template.AppliesGender),
                    new SqlParameter("@Remark",template.Remark)

           };
            int rowAffected = 0;
            try
            {
                rowAffected = (int) SqlHelper.ExecuteNonQuery(sql, false, pms);
            }
            catch
            {

            }
            return rowAffected;
        }
    }
}
